HAVING
This lesson demonstrates how to use the HAVING clause.
We'll cover the following
Having Clause#
We are familiar with the WHERE clause that can be used to filter rows. In the same way, the HAVING clause allows us to filter groups. At times, the HAVING clause can be used to filter rows to display but that is not the intended use and can make the query slower. The HAVING clause should be used to decide what rows form each group. Remember the HAVING clause works on groups of rows whereas the WHERE clause works on individual rows. We’ll work with a couple of queries in this lesson to clarify the concept.
Example Syntax#
SELECT col1, AggregateFunction(col3) AS count
FROM table
GROUP BY col1, col2, … coln
HAVING count > 75
ORDER BY col2;
Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/24lesson.sh and wait for the MySQL prompt to start-up.
- In the lesson on the GROUP BY clause we wrote a query to find out the average net worth of actors by their marital status. Now we can filter on the group results so that we only see those groups whose net worth is either greater than 450 million or less than 250 million.
SELECT MaritalStatus, AVG(NetworthInMillions) AS NetWorth
FROM Actors
GROUP BY MaritalStatus
HAVING NetWorth > 450 OR NetWorth < 250;
Observe that one group has been filtered out because it didn’t meet the conditions specified in the HAVING clause. Also notice, we define an alias for the function result so that we don’t have to rewrite the function in the conditions for the HAVING clause.
-
Usually, the HAVING clause is used with aggregate functions. If you find yourself writing a HAVING clause that uses a column or expression that isn’t in the SELECT clause, it is likely you should be using the WHERE clause instead. For instance, consider the following query, which uses the marital status column in the HAVING clause.
SELECT MaritalStatus, AVG(NetworthInMillions) AS NetWorth
FROM Actors
GROUP BY MaritalStatus
HAVING MaritalStatus='Married';
The same query using the WHERE clause can be rewritten as follows:
SELECT MaritalStatus, AVG(NetworthInMillions) AS NetWorth FROM Actors WHERE MaritalStatus='Married' GROUP BY MaritalStatus;
